﻿using MySqlConnector;
using System.Data;
using System.Reflection;

namespace Unit5.Utils
{
    public class DbHelper
    {
        private static string? _connString;

        // 静态构造方法
        // 第一次访问这个类的时候，会执行一次静态构造

        static DbHelper()
        {
            ConfigurationBuilder configuration = new ConfigurationBuilder(); // 读取配置文件
            var config = configuration.SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile(file =>
                {
                    file.Path = "/appsettings.json";
                    file.Optional = false;
                    file.ReloadOnChange = true;
                }).Build();
            _connString = config.GetConnectionString("MySql");
            Console.WriteLine($"_connString:{_connString}");
        }

        private static MySqlCommand PrepareCommand(MySqlConnection conn, string sql, CommandType cmdType, params MySqlParameter[]? parameters)
        {
            using MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }

        /**
            执行增删改的操作
         */
        public static int ExecuteNonQuery(string sql, params MySqlParameter[]? parameters)
        {
            using MySqlConnection conn = new(_connString);
            conn.Open();
            return PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteNonQuery();
        }

        public static int ExecuteNoQuery(string sql, CommandType cmdType = CommandType.Text, params MySqlParameter[]? parameters)
        {
            using MySqlConnection conn = new(_connString);
            conn.Open();
            return PrepareCommand(conn, sql, cmdType, parameters).ExecuteNonQuery();
        }

        public static int ExecuteScalar(string sql, params MySqlParameter[]? parameters)
        {
            using MySqlConnection conn = new(_connString);
            conn.Open();
            return Convert.ToInt32(PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteScalar());
        }

        public static int ExecuteScalar(string sql, CommandType cmdType = CommandType.Text, params MySqlParameter[]? parameters)
        {
            using MySqlConnection conn = new(_connString);
            conn.Open();
            return Convert.ToInt32(PrepareCommand(conn, sql, cmdType, parameters).ExecuteScalar());
        }

        public static List<T> GetList<T>(string sql, params MySqlParameter[]? parameters) where T : class, new()
        {
            MySqlDataAdapter adapter = new(PrepareCommand(new(_connString), sql, CommandType.Text, parameters));
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return ToList<T>(dataTable);
        }

        public static List<T> GetList<T>(string sql, CommandType cmdType = CommandType.Text, params MySqlParameter[]? parameters) where T : class, new()
        {
            using MySqlDataAdapter adapter = new(PrepareCommand(new(_connString), sql, cmdType, parameters));
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return ToList<T>(dataTable);
        }

        public static List<T> ToList<T>(DataTable dataTable) where T : class, new()
        {
            Type t = typeof(T);
            PropertyInfo[] propertys = t.GetProperties();
            List<T> list = new List<T>();
            string typeName = string.Empty;

            foreach (DataRow dr in dataTable.Rows)
            {
                T entity = new T();
                foreach (PropertyInfo pi in propertys)
                {
                    typeName = pi.Name;
                    if (dataTable.Columns.Contains(typeName))
                    {
                        if (!pi.CanWrite) continue;
                        object value = dr[typeName];
                        if (value == DBNull.Value) continue;
                        if(pi.PropertyType == typeof(string))
                        {
                            pi.SetValue(entity, value.ToString(), null);
                        }
                        else if(pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                        {
                            pi.SetValue(entity,int.Parse(value.ToString()), null);
                        }
                        else if(pi.PropertyType == typeof(DateTime?)||pi.PropertyType== typeof(DateTime))
                        {
                            pi.SetValue(entity, DateTime.Parse(value.ToString()), null);
                        }
                        else if(pi.PropertyType == typeof(float))
                        {
                            pi.SetValue(entity,float.Parse(value.ToString()),null);
                        }
                        else if(pi.PropertyType == typeof(double))
                        {
                            pi.SetValue(entity,double.Parse(value.ToString()),null);
                        }
                        else
                        {
                            pi.SetValue(entity, value, null);
                        }
                    }
                }
                list.Add(entity);
            }
            return list;
        }
    }
}
